from flask import Flask,Response,jsonify
from db import get_db
import json
import pymysql

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False

@app.route('/')
def index():
    return "123"

@app.route('/query')
def query_data():
    db = get_db()
    try:
        with db.cursor() as cursor:
            # 执行查询
            sql = "SELECT * FROM users" # 写sql语句
            cursor.execute(sql)  # 使用参数化查询防止SQL注入,发送sql语句
            results = cursor.fetchall() # 拿到返回数据
            # return {'data': results}  # 将数据返回给浏览器

            #将数据返回给浏览器,并支持中文(没有乱码)
            return Response(
                json.dumps(results, ensure_ascii=False),
                content_type='application/json; charset=utf-8'
            )
    finally:
        db.close()

@app.route('/insert', methods=['POST'])
def insert_data():
    db = get_db()
    try:
        with db.cursor() as cursor:
            # 插入数据
            sql = "INSERT INTO users (username, email) VALUES (%s, %s)"
            cursor.execute(sql, ('yyy', 'yyy@example.com'))
            db.commit()  # 提交事务
            return {'message': 'Insert successful'}, 201
    except Exception as e:
        db.rollback()  # 出错时回滚
        return {'error': str(e)}, 500
    finally:
        db.close()

@app.route('/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    try:
        with get_db() as conn:
            with conn.cursor() as cursor:
                cursor.execute("DELETE FROM users WHERE id=%s", (user_id,))
                if cursor.rowcount == 0:
                    return jsonify({"error": "User not found"}), 404
                conn.commit()
                return jsonify({"message": "Deleted successfully"})
    except pymysql.Error as e:
        return jsonify({"error": str(e)}), 500


if __name__ == '__main__':
    app.run(debug=True)